<?php

class App_model extends CI_Model {
    
    function applogin($username, $password) {
        $this->db->where("username", $username);
        $this->db->where("password", $password);
        $query = $this->db->get("user");
        return $query->row_array();
    }
    
    function getData(){
        $tableName = $_POST['tablename'];
        $condition = $_POST['condition'];
        $page = $_POST['page'];
        $nums = $_POST['nums'];
        $startnum = $page*$nums;
        
        $sql = "";
        $countsql = "";
        if($tableName == "Candidate"){
            $sql = "select w.*,i.name intermediaryName from lfsrlzy_workers w "
                    . "left join lfsrlzy_company i on (w.companyid = i.id and i.type = 2) "
                    . "where (w.name like '%".$condition."%' or w.position like '%".$condition.
                    "%' or w.speciality like '%".$condition."%' or i.name like '%".$condition."%') "
                    . "limit {$startnum},{$nums}";
            $countsql = "select w.*,i.name intermediaryName from lfsrlzy_workers w "
                    . "left join lfsrlzy_company i on (w.companyid = i.id and i.type = 2) "
                    . "where (w.name like '%".$condition."%' or w.position like '%".$condition.
                    "%' or w.speciality like '%".$condition."%' or i.name like '%".$condition."%') ";
        }else if($tableName == "Company"){
            $sql = "select c.*,i.name industryName from lfsrlzy_company c "
                    . "left join lfsrlzy_industry i on c.industry = i.id "
                    . "where c.type = 1 and (c.name like '%".$condition."%' or i.name like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.position like '%".$condition."%') "
                    . "limit {$startnum},{$nums}";
            $countsql = "select c.*,i.name industryName from lfsrlzy_company c "
                    . "left join lfsrlzy_industry i on c.industry = i.id "
                    . "where c.type = 1 and (c.name like '%".$condition."%' or i.name like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.position like '%".$condition."%') ";
        }else if($tableName == "Intermediary"){
            $sql = "select c.* from lfsrlzy_company c "
                    . "where c.type = 2 and (c.name like '%".$condition."%' or c.region like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.number like '%".$condition."%' or c.qq like '%".$condition."%') "
                    . "limit {$startnum},{$nums}";
            $countsql = "select c.* from lfsrlzy_company c "
                    . "where c.type = 2 and (c.name like '%".$condition."%' or c.region like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.number like '%".$condition."%' or c.qq like '%".$condition."%') ";
        }else if($tableName == "HouseAgent"){
            $sql = "select c.* from lfsrlzy_company c "
                    . "where c.type = 3 and (c.name like '%".$condition."%' or c.region like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.number like '%".$condition."%' or c.qq like '%".$condition."%') "
                    . "limit {$startnum},{$nums}";
            $countsql = "select c.* from lfsrlzy_company c "
                    . "where c.type = 3 and (c.name like '%".$condition."%' or c.address like '%".$condition."%' or "
                    . "c.person like '%".$condition."%' or c.number like '%".$condition."%' or c.qq like '%".$condition."%') ";
        }else if($tableName == "Order"){
            $sql = "select o.*,c.name from lfsrlzy_order o "
                    . "left join lfsrlzy_company c on c.id = o.companyid "
                    . "where (o.sn like '%".$condition."%' or c.name like '%".$condition."%' or "
                    . "o.position like '%".$condition."%' or o.salary like '%".$condition."%') "
                    . "limit {$startnum},{$nums}";
            $countsql = "select o.*,c.name from lfsrlzy_order o "
                    . "left join lfsrlzy_company c on c.id = o.companyid "
                    . "where (o.sn like '%".$condition."%' or c.name like '%".$condition."%' or "
                    . "o.position like '%".$condition."%' or o.salary like '%".$condition."%') ";
        }
        return array(
            "data"=>$this->db->query($sql)->result_array(),
            "total"=>count($this->db->query($countsql)->result_array())
        );
    }
    
    function getProfile(){
        $type = $_POST['type'];
        $userid = $_POST['userid'];
        $role = $_POST['role'];
        $condition = $_POST['condition'];
        $page = $_POST['page'];
        $nums = $_POST['nums'];
        $startnum = $page*$nums;
        
        $str_sql = "select * from lfsrlzy_profile where status = {$type} and name like '%".$condition."%'";
        if($role*1 == 1){
            $str_sql .= " and employeeid = ".$userid;
            $this->db->where("employeeid",$userid);
        }
        $str_sql .=" limit {$startnum},{$nums}";
        $this->db->where("type",$type);
        $this->db->like('name', $condition);
        $this->db->from('profile');
        return array(
            'total' => $this->db->count_all_results(),
            'data' => $this->db->query($str_sql)->result_array(),
        );
    }
    
    function getReminder(){
        $type = $_POST['type'];
        $userid = $_POST['userid'];
        $role = $_POST['role'];
        $condition = $_POST['condition'];
        $page = $_POST['page'];
        $nums = $_POST['nums'];
        $startnum = $page*$nums;
        
        $sql = "select n.*,w.name,e.name as createby from lfsrlzy_notice n "
                . "left join lfsrlzy_workers w on w.id = n.association "
                . "left join lfsrlzy_employee e on n.employeeid = e.userid "
                . "where (n.title like '%{$condition}%' or w.name like '%{$condition}%') ";
        if($type*1 != 241){
            $sql .= " and n.type = {$type}";
        }else{
            $sql .= " and (n.type = 2 or n.type = 3 or n.type = 4 or n.type = 5)";
        }
        if($role == 10){
            $workerids = $this->getWorkeridsByCompanyId($userid);
            $sql .= " and w.id in ($workerids)";
        }
        
        $count = count($this->db->query($sql)->result_array());
        $sql .= " order by n.noticedate desc limit {$startnum},{$nums}";
        return array(
            'total' => $count,
            'data' => $this->db->query($sql)->result_array(),
        );
    }
    
    public function getApplicationCandidate(){
        $condition = $_POST['condition'];
        $page = $_POST['page'];
        $nums = $_POST['nums'];
        $startnum = $page*$nums;
        
        $sql = "select w.*,i.name intermediaryName from lfsrlzy_workers w "
                . "left join lfsrlzy_company i on (w.companyid = i.id and i.type = 2) "
                . "where (w.name like '%".$condition."%' or w.position like '%".$condition.
                "%' or w.speciality like '%".$condition."%' or i.name like '%".$condition."%') and "
                . "w.status = 0 ";
        
        $userid = $_POST['userid'];
        $role = $_POST['role'];
        if($role == 10){
            $workerids = $this->getWorkeridsByCompanyId($userid);
            $sql .= " and w.id in ($workerids)";
        }
        
        $count = count($this->db->query($sql)->result_array());
        $sql .= "limit {$startnum},{$nums}";
        return array(
            "data"=>$this->db->query($sql)->result_array(),
            "total"=>$count
        );
    }
    
    public function getOtherNotice(){
        $sql = "select * from lfsrlzy_notice where type = 2 or type = 3 or type = 4 or type = 5";
        return $this->db->query($sql)->result_array();
    }
    
    public function getApplicationWorkerByCompanyId($companyid){
        $sql1 = "select p.workerid as wid from lfsrlzy_profile p,lfsrlzy_order o,lfsrlzy_company c "
                . "where p.ordersn = o.id and o.companyid = c.id and c.status = 0 and c.id = ".$companyid;
        $p1 = $this->db->query($sql1)->result_array();
        
        $sql2 = "select p.workerid as wid from lfsrlzy_profile p,lfsrlzy_company c where p.companyid = c.id and c.status = 0 and c.id = ".$companyid;
        $p2 = $this->db->query($sql2)->result_array();
        
        $workerids = array();
        foreach ($p1 as $key => $p) {
            $wid = $p['wid'];
            $workerids[] = $wid;
        }
        foreach ($p2 as $key => $p) {
            $wid = $p['wid'];
            if(!in_array($wid,$workerids)){
                $workerids[] = $wid;
            }
        }
        return $workerids;
    }
    
    public function getNoticeByCompanyId($companyid,$type=-1,$noticetime=""){
        $workerids = $this->getWorkeridsByCompanyId($companyid);
        $sql = "select * from lfsrlzy_notice where association in ($workerids)";
        if($type != -1 && $type != 100){
            $sql .= " and type = ".$type;
        }else if($type == 100){
            $sql .= " and (type = 2 or type = 3 or type = 4 or type = 5)";
        }
        if($noticetime != ""){
            $sql .= " and noticedate = '$noticetime'";
        }
        
        $result = $this->db->query($sql)->result_array();
        return $result;
    }
    
    public function getWorkeridsByCompanyId($companyid){
        $sql1 = "select p.workerid as wid from lfsrlzy_profile p,lfsrlzy_order o where p.ordersn = o.id and o.companyid = ".$companyid;
        $p1 = $this->db->query($sql1)->result_array();
        
        $sql2 = "select p.workerid as wid from lfsrlzy_profile p where p.companyid = ".$companyid;
        $p2 = $this->db->query($sql2)->result_array();
        
        $workerids = array();
        foreach ($p1 as $key => $p) {
            $wid = $p['wid'];
            $workerids[] = $wid;
        }
        foreach ($p2 as $key => $p) {
            $wid = $p['wid'];
            if(!in_array($wid,$workerids)){
                $workerids[] = $wid;
            }
        }
        $workerids = implode(',',$workerids);
        return $workerids;
    }
}

?>
